package cn.itcast.demo6.mapper;

import cn.itcast.demo6.pojo.OrderList;
import cn.itcast.demo6.pojo.Purse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * ClassName: PurseMapper
 * Package: com.itcast.demo5.mapper
 * Description:
 *
 * @Author MLH
 * @Create 2023/4/18 9:20
 * @Version 1.0
 */
@Mapper
public interface PurseMapper{

    /**
     * 查询总记录数
     * @return
     */
    @Select("SELECT COUNT(*) FROM p_order")
    int getOrderCount();

    /**
     * 分页查询订单数
     * @param page 页数
     * @param pers 每页记录数
     * @return
     */
    @Select("SELECT o.id, o.created_at, o.updated_at, u.nickname, u.mobile, " +
            "o.product_name, o.total_price, o.count, o.unit_price, o.status, o.pay_type " +
            "FROM p_order o " +
            "INNER JOIN p_user u ON o.user_id = u.id " +
            "ORDER BY o.id DESC " +
            "LIMIT #{pers} OFFSET #{page}")
    List<OrderList> getOrderList(int page, int pers);

    /**
     * 查询有订单记录的用户数
     * @return
     */
    @Select("SELECT COUNT(DISTINCT user_id) FROM p_order")
    int getTotalUserCount();

    /**
     * 查询拥有订单用户最后的订单信息
     * @param page 页数
     * @param pers 每页记录数
     * @return
     */
    @Select("SELECT o.id, o.created_at, o.updated_at, u.nickname, u.mobile, o.product_name," +
            " o.total_price, o.count, o.unit_price, o.status, o.pay_type " +
            "FROM p_order o " +
            "INNER JOIN p_user u ON o.user_id = u.id " +
            "INNER JOIN (SELECT user_id, MAX(created_at) AS max_created_at FROM p_order GROUP BY user_id) o2 " +
            "ON o.user_id = o2.user_id AND o.created_at = o2.max_created_at " +
            "ORDER BY o.id DESC " +
            "LIMIT #{pers} OFFSET #{page}")
    List<OrderList> getLastOrderPerUser(int page, int pers);



}
